
* Set Directory
cd "...Replication Files"

use "Data/BC_PPD.dta", clear

keep if fy >= 2015
drop if fy == 2023
drop if fy == .

rename statename state

replace contrib_er_state = 0 if contrib_er_state == .
replace contrib_er_other = 0 if contrib_er_other == .
gen contrib_er_excess = contrib_er_state + contrib_er_other
replace contrib_er_excess = . if contrib_er_regular == .
replace contrib_er_state = . if contrib_er_regular == .
replace contrib_er_excess = . if contrib_er_tot == .

gen ex_missing = contrib_er_excess == .

sort govtname fy
by govtname: egen miss_ex = sum(ex_missing)


gen state_indicator = state == govtname

collapse (sum) contrib_tot contrib_er_tot contrib_er_excess contrib_er_state requiredcontribution ex_missing actassets_gasb actliabilities_gasb payroll state_indicator, by(state fy)

gen govtname = state


* Merge in Population Data
merge 1:1 govtname fy using "Data/Population_AllGovs.dta", keepus(population)
drop if _merge == 2
drop _merge

* Merge in Unemployment Data
merge 1:1 govtname fy using "Data/unemployment_allgovs.dta", keepus(percent_unemployed)
drop if _merge == 2
drop _merge


* Merge in SNAP data
merge 1:1 govtname fy using "Data/Snap_allgovs.dta", keepus(snap_hh_share)
drop if _merge == 2
drop _merge

* Merge in data on state aid 
merge m:1 state using "Data/cleanedAidRepsPop_all.dta" 
keep if _merge == 3
drop _merge
rename pop_2020 statepop_2020 

gen reps_per_mil_post = 0
replace reps_per_mil_post = (all_reps/(statepop_2020 / 1000000)) if fy == 2021 | fy == 2022

* Drop DC 
drop if state == "District of Columbia"

gen wider_aid_21 = state_local_aid_21
gen wider_aid_22 = state_local_aid_22

gen wider_aid_post = 0
replace wider_aid_post = (wider_aid_21 * 1000000 / statepop_2020) if fy == 2021
replace wider_aid_post = (wider_aid_22 * 1000000 / statepop_2020) if fy == 2022

gen narrow_aid_21 = state_agency_localGovt_aid_21
gen narrow_aid_22 = state_agency_localGovt_aid_22

gen narrow_aid_post = 0
replace narrow_aid_post = (narrow_aid_21 * 1000000 / statepop_2020) if fy == 2021
replace narrow_aid_post = (narrow_aid_22 * 1000000 / statepop_2020) if fy == 2022

* Merge in CPI
* https://www.minneapolisfed.org/about-us/monetary-policy/inflation-calculator/consumer-price-index-1913-
merge m:1 fy using "Data/CPI Price Deflator.dta"
keep if _merge == 3
drop _merge

* Deflate aid using CPI
foreach y in contrib_er_tot contrib_er_excess contrib_er_state requiredcontribution wider_aid_post narrow_aid_post actliabilities_gasb  { 
	replace `y' = `y' * 255.7 / price_deflator 
}

sort state govtname fy
egen gov_group = group(govtname)
egen state_group = group(state)
xtset gov_group fy


foreach var in percent_unemployed snap_hh_share {
	gen l_`var'= l.`var'
}

gen excess_pop = contrib_er_excess * 1000 / l.population

drop if ex_missing != 0

save "Data/Analytic_sample_aggregated.dta", replace


